package com.ruoyi.gd.wgd.service.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.IdUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import com.amazonaws.util.IOUtils;
import com.ruoyi.common.core.domain.entity.SysDictData;
import com.ruoyi.common.core.domain.model.LoginUser;
import com.ruoyi.common.helper.LoginHelper;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.common.core.page.TableDataInfo;
import com.ruoyi.common.core.domain.PageQuery;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.ruoyi.gd.fj.domain.bo.ZFujianBo;
import com.ruoyi.gd.fj.domain.vo.ZFujianVo;
import com.ruoyi.gd.fj.service.IZFujianService;
import com.ruoyi.gd.wgdlog.domain.ZWgdLog;
import com.ruoyi.gd.wgdlog.mapper.ZWgdLogMapper;
import com.ruoyi.gd.ywd.domain.ZYwd;
import com.ruoyi.gd.ywd.service.IZYwdService;
import com.ruoyi.system.service.ISysDictTypeService;
import lombok.RequiredArgsConstructor;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import com.ruoyi.gd.wgd.domain.bo.ZWgdBo;
import com.ruoyi.gd.wgd.domain.vo.ZWgdVo;
import com.ruoyi.gd.wgd.domain.ZWgd;
import com.ruoyi.gd.wgd.mapper.ZWgdMapper;
import com.ruoyi.gd.wgd.service.IZWgdService;

import javax.annotation.Resource;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URISyntaxException;
import java.net.URL;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 完工单Service业务层处理
 *
 * @author ruoyi
 * @date 2024-03-17
 */
@RequiredArgsConstructor
@Service
public class ZWgdServiceImpl implements IZWgdService {

    private final ZWgdMapper baseMapper;
    @Resource
    private ISysDictTypeService dictTypeService;
    @Resource
    private ZWgdLogMapper zwLogMapper;
    @Resource
    private IZYwdService ywdService;

    @Value("${upload.path}")
    private String uploadPath;

    @Resource
    private IZFujianService fujianService;

    /**
     * 查询完工单
     */
    @Override
    public ZWgdVo queryById(String id){
        return baseMapper.selectVoById(id);
    }

    /**
     * 查询完工单列表
     */
    @Override
    public TableDataInfo<ZWgdVo> queryPageList(ZWgdBo bo, PageQuery pageQuery) {
        LambdaQueryWrapper<ZWgd> lqw = buildQueryWrapper(bo);
        Page<ZWgdVo> result = baseMapper.selectVoPage(pageQuery.build(), lqw);
        return TableDataInfo.build(result);
    }

    @Override
    public TableDataInfo<ZWgdVo> queryPageHomeList(ZWgdBo bo, PageQuery pageQuery) {
        List<ZWgdVo> paginate = paginate(baseMapper.selectHomePage(bo), pageQuery.getPageNum(), pageQuery.getPageSize());
        return TableDataInfo.build(paginate);
    }

    // 手动分页
    public List<ZWgdVo> paginate(List<ZWgdVo> list, int page, int pageSize) {
        int fromIndex = (page - 1) * pageSize;
        int toIndex = Math.min(fromIndex + pageSize, list.size());
        return list.subList(fromIndex, toIndex);
    }

    /**
     * 查询完工单列表
     */
    @Override
    public List<ZWgdVo> queryList(ZWgdBo bo) {
        LambdaQueryWrapper<ZWgd> lqw = buildQueryWrapper(bo);
        return baseMapper.selectVoList(lqw);
    }

    private LambdaQueryWrapper<ZWgd> buildQueryWrapper(ZWgdBo bo) {
        LambdaQueryWrapper<ZWgd> lqw = Wrappers.lambdaQuery();
        try {
            // 处理空格数据
            bo.setKhname(StringUtils.trimField(bo, "khname"));
            bo.setZbbh(StringUtils.trimField(bo, "zbbh"));
            bo.setWgbh(StringUtils.trimField(bo, "wgbh"));

            LoginUser loginUser = LoginHelper.getLoginUser();
            // 1.通过字典数据，哪些用户可以获取所有数据
            List<String> khGetAllData = dictTypeService.selectDictDataByType("kh_getAllData").stream().map(SysDictData::getDictValue).collect(Collectors.toList());
            // 判断字典数据中的id在集合中是否存在
            boolean b = khGetAllData.stream().anyMatch(val -> val.equals(loginUser.getUserId().toString()));
            // 如果当前用户不存在则查询所有
            if (!b) {
                lqw.eq(true, ZWgd::getCreateId, loginUser.getUserId());
            }
            lqw.eq(StringUtils.isNotBlank(bo.getFj()), ZWgd::getFj, bo.getFj());
            lqw.eq(StringUtils.isNotBlank(bo.getFjpdf()), ZWgd::getFjpdf, bo.getFjpdf());
//            lqw.like(StringUtils.isNotBlank(bo.getSfykfp()), ZWgd::getSfykfp, bo.getSfykfp());
            lqw.like(StringUtils.isNotBlank(bo.getKhname()), ZWgd::getKhname, bo.getKhname());
            lqw.like(StringUtils.isNotBlank(bo.getZbbh()), ZWgd::getZbbh, bo.getZbbh());
            lqw.like(StringUtils.isNotBlank(bo.getWgbh()), ZWgd::getWgbh, bo.getWgbh());
//            lqw.like(StringUtils.isNotBlank(bo.getSfhk()), ZWgd::getSfhk, bo.getSfhk());
            lqw.eq(StringUtils.isNotBlank(bo.getYhyqr()), ZWgd::getYhyqr, bo.getYhyqr());

            // 判断是否回款
            if (StringUtils.isNotEmpty(bo.getSfhk())) {  // 是否已下场
                List<String> strings = Arrays.asList(bo.getSfhk().split(","));
                lqw.in(ZWgd::getSfhk, strings);
            }

            // 判断是否已开票
            if (StringUtils.isNotEmpty(bo.getSfykfp())) {  // 是否已下场
                List<String> strings = Arrays.asList(bo.getSfykfp().split(","));
                lqw.in(ZWgd::getSfykfp, strings);
            }

            // 添加时间条件
            if (bo.getParams()!=null && bo.getParams().size() > 0) {
                // 设置开票日期
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                Object start = bo.getParams().get("startTime");
                Object end = bo.getParams().get("endTime");
                if (null != start && null != end) {
                    Date startTime = format.parse((String) start);
                    Date endTime = format.parse((String) end);
                    if (startTime != null && endTime != null) {
                        lqw.ge(ZWgd::getKprq, startTime);
                        lqw.le(ZWgd::getKprq, endTime);
                    }
                }
                // 设置完工日期
                Object startWgTime = bo.getParams().get("startWgTime");
                Object endWgTime = bo.getParams().get("endWgTime");
                if (null != startWgTime && null != endWgTime) {
                    Date startTime = format.parse((String) startWgTime);
                    Date endTime = format.parse((String) endWgTime);
                    if (startTime != null && endTime != null) {
                        lqw.ge(ZWgd::getWgrq, startTime);
                        lqw.le(ZWgd::getWgrq, endTime);
                    }
                }
                // 设置判断当前的完善状态
                Object wszt = bo.getParams().get("wszt");
                if (null != wszt && StringUtils.isNotEmpty(wszt.toString())) {
                    // 判断当前的完善状态
                    String[] split = wszt.toString().split(",");
                    if (split.length == 1) {
                        for (String s : split) {
                            // 判断当前的添加是否有完善
                            if ("ws".equals(s)) {
                                lqw.isNotNull(ZWgd::getFj);
                            }
                            if ("dws".equals(s)) {
                                lqw.isNull(ZWgd::getFj);
                            }
                        }
                    }
                }
                // 设置当前的创建时间
                SimpleDateFormat formatCreate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                Object s = bo.getParams().get("startCreateTime");
                Object e = bo.getParams().get("endCreateTime");
                if (null != s && null != e) {
                    Date startTime = formatCreate.parse((String) s);
                    Date endTime = formatCreate.parse((String) e);
                    if (startTime != null && endTime != null) {
                        lqw.ge(ZWgd::getCreateTime, startTime);
                        lqw.le(ZWgd::getCreateTime, endTime);
                    }
                }

            }
            if(!"admin".equals(loginUser.getUsername())) {
                List<String> strings = Arrays.asList("0");
                lqw.in(ZWgd::getDelFlag, strings);
            }
            lqw.orderByDesc(ZWgd::getWgrq);
        }catch (Exception e) {
            e.printStackTrace();
        }
        return lqw;
    }

    /**
     * 新增完工单
     */
    @Override
    public Boolean insertByBo(ZWgdBo bo) {
        ZWgd add = BeanUtil.toBean(bo, ZWgd.class);
        LoginUser loginUser = LoginHelper.getLoginUser();
        add.setCreateId(loginUser.getUserId()+"");
        validEntityBeforeSave(add);
        boolean flag = baseMapper.insert(add) > 0;
        if (flag) {
            bo.setId(add.getId());
            // 新增后，通过编号更新业务单数据，将状态更改为下场完成
            String zbbh = add.getZbbh();
            ywdService.updateByZbbh(zbbh);
        }
        return flag;
    }

    /**
     * 修改完工单
     */
    @Override
    public Boolean updateByBo(ZWgdBo bo) {
        ZWgd update = BeanUtil.toBean(bo, ZWgd.class);
        validEntityBeforeSave(update);
        boolean b = baseMapper.updateById(update) > 0;
        if (StringUtils.isNotEmpty(bo.getIsLog()) && bo.getIsLog().equals("true")) {
            ZWgdLog zWgdLog = new ZWgdLog();
            zWgdLog.setWgdid(update.getId());
            zWgdLog.setSfhk(update.getSfhk());
            zWgdLog.setHkje(update.getHkje());
            zWgdLog.setHksj(update.getHksj());
            zWgdLog.setCreatetime(new Date());
            zwLogMapper.insert(zWgdLog);
        }
        return b;
    }

    /**
     * 保存前的数据校验
     */
    private void validEntityBeforeSave(ZWgd entity){
        //TODO 做一些数据校验,如唯一约束
    }

    /**
     * 批量删除完工单
     */
    @Override
    public Boolean deleteWithValidByIds(Collection<String> ids, Boolean isValid) {
        if(isValid){
            //TODO 做一些业务上的校验,判断是否需要校验
        }
        return baseMapper.batchDeleteIds(ids) > 0;
    }

    @Override
    public int updateYhyqrZt(List<String> asList) {
        int row = baseMapper.updateYhyqrZt(asList);
        return row;
    }

    @Override
    public List<ZWgdVo> selectByIds(String[] split) {
        return baseMapper.selectByIds(split);
    }

    @Override
    public int batchUpdateById(List<String> wgdIds) {
        return baseMapper.batchUpdateById(wgdIds);
    }

    @Override
    public List<ZWgdVo> selectWcl() {
        return baseMapper.selectWcl();
    }

    @Override
    public List<ZWgdVo> queryListByIds(String ids) {
        String[] split = ids.split(",");
        List<ZWgdVo> list = baseMapper.selectByIds(split);
        return list;
    }

    @Override
    public Boolean authData(String[] ids) {
        return baseMapper.updateBatchByIds(ids) > 0;
    }

    /**
     * 生成报价单
     * 逻辑：
     * 1.获取当前的完工单，将完工单的列表数据进行解析为List
     * 2.将解析的List数据基于模板插入到报价单模板中
     * 3.将生成的报价单的流返回给前端
     */
    @Override
    public ZFujianBo genBjd(ZWgdBo bo) {
        // 通过当前的附件id获取当前的附件
        if (StringUtils.isNotEmpty(bo.getFj())) {
            List<ZFujianVo> zFujianVos = fujianService.queryListByIdOrdery(bo.getFj());
            for (ZFujianVo zFujianVo : zFujianVos) {
                String fjpath = uploadPath + zFujianVo.getFjpath();

                FileInputStream fis = null;
                FileOutputStream fos = null;
                FileInputStream fileInputStream = null;
                FileOutputStream fileOutputStream = null;
                try  {
                    fis = new FileInputStream(fjpath);
                    ZipSecureFile.setMinInflateRatio(0.0);
                    Workbook workbook = new XSSFWorkbook(fis);
                    Sheet sheet = workbook.getSheetAt(0); // 假设我们要操作第一个sheet
                    JSONObject jsonData = getDatas(sheet, sheet.getLastRowNum());
                    JSONArray wgdList = new JSONArray();
                    for (int row = 0; row <= sheet.getLastRowNum(); row++) {
                        // 获取一行数据
                        Row cells = sheet.getRow(row);
                        Integer slRow = jsonData.getInt("sl_row");
                        if (row > slRow) {
                            Integer slColumn = jsonData.getInt("sl_column");
                            Integer djColumn = jsonData.getInt("dj_column");
                            Cell bh = cells.getCell(jsonData.getInt("xh_column"));  // 编号
                            Cell yqmc = cells.getCell(jsonData.getInt("yqmc_column"));  // 仪器名称
                            Cell xh = cells.getCell(jsonData.getInt("xhgg_column"));  // 型号规格
                            Cell zzc  = cells.getCell(jsonData.getInt("zzc_column"));  // 制造厂
                            Cell ccbh  = cells.getCell(jsonData.getInt("ccbh_column"));  // 出厂编号
                            Cell glbh  = cells.getCell(jsonData.getInt("glbh_column"));  // 管理编号
                            Cell sl = cells.getCell(slColumn);    // 数量
                            Cell dj = cells.getCell(djColumn);    // 单价
                            Cell xj = cells.getCell(jsonData.getInt("xj_column"));    // 小计
                            Cell fwfs = cells.getCell(jsonData.getInt("fwfs_column"));    // 服务方式
                            Cell bz = cells.getCell(jsonData.getInt("bz_column"));    // 备注
                            if (bh != null && (bh.toString().contains("校准总费用")
                                      || bh.toString().contains("现场服务费"))) {
                                JSONObject object = new JSONObject();
                                object.set("bh", row + 1);
                                object.set("yqmc", bh.toString());
                                object.set("xj", xj.toString());
                                wgdList.add(object);
                            } else if(bh != null && (bh.toString().contains("含6%专用增值税发票"))) {
//                                JSONObject object = new JSONObject();
//                                object.set("bh", row + 1);
//                                object.set("yqmc", bh.toString());
//                                object.set("xj", xj.toString());
//                                wgdList.add(object);
                                break;
                            } else if (bh != null && StringUtils.isNotEmpty(bh.toString())     // 编号
                                && yqmc != null && StringUtils.isNotEmpty(yqmc.toString())  // 仪器名称
                                && xh != null && StringUtils.isNotEmpty(xh.toString())      // 型号
                                && dj != null && StringUtils.isNotEmpty(dj.toString())    // 制造厂
                                && xj != null && StringUtils.isNotEmpty(xj.toString())) {  // 出厂编号
                                JSONObject object = new JSONObject();
                                object.set("bh", bh.toString());
                                object.set("yqmc", yqmc.toString());
                                object.set("xh", xh.toString());
                                object.set("zzc", zzc.toString());
                                object.set("ccbh", ccbh.toString());
                                object.set("glbh", glbh.toString());
                                // 设置数量
                                Double slNum = 0.0;
                                if (sl != null) {
                                    slNum = Double.parseDouble(sl.toString());
                                }
                                object.set("sl", slNum);
                                // 设置单价
                                Double djNum = 0.0;
                                if (dj != null) {
                                    djNum = Double.parseDouble(dj.toString());
                                }
                                object.set("dj", djNum);
                                String sjZm = new CellReference(slRow, slColumn, false, false).formatAsString();
                                String djZm = new CellReference(slRow, djColumn, false, false).formatAsString();
                                String gs = sjZm + "*" + djZm;
                                object.set("xj", gs);
                                object.set("fwfs", fwfs.toString());
                                object.set("bz", bz.toString());
                                wgdList.add(object);
                            }
                        }
                    }

                    // 将JSONArray转换为List
                    List<Object> list = new ArrayList<>(wgdList);
                    // 交换最后两个元素
                    Collections.swap(list, list.size() - 1, list.size() - 2);
                    // 将List转换回JSONArray
                    wgdList = new JSONArray(list);

                    // 获取模板的路径
                    String excelUrl = getTemplatePath("template/bjdmb.xlsx");
                    fileInputStream = new FileInputStream(excelUrl);
                    Workbook fileWorkbook = new XSSFWorkbook(fileInputStream);
                    Sheet sheetData = fileWorkbook.getSheetAt(0);
                    // 在第二行，第一列添加委托单位  row 1 column 0
                    sheetData.getRow(2).getCell(0).setCellValue("委托单位（甲方）："+ bo.getKhname());
                    // 在第五行，第一列添加地址  row 4 column 0
                    sheetData.getRow(5).getCell(0).setCellValue("地  址："+bo.getDz());
                    // 在第三十行，第五列添加日期 row 29 column 4
                    SimpleDateFormat format = new SimpleDateFormat("yyyy.MM.dd");
                    sheetData.getRow(25).getCell(4).setCellValue("日期："+ format.format(bo.getBjrq()));

                    // 在第7行（索引为6）前插入新行，将第7行及之后的行下移
                    sheetData.shiftRows(7, sheetData.getLastRowNum(), wgdList.size());

                    Font font = fileWorkbook.createFont();
                    font.setFontName("SimSun");  // 设置字体为宋体
                    // 创建单元格样式
                    CellStyle style = fileWorkbook.createCellStyle();
                    // 将字体应用到单元格样式
                    style.setFont(font);
                    // 设置单元格文字居中
                    style.setAlignment(HorizontalAlignment.CENTER);
                    style.setVerticalAlignment(VerticalAlignment.CENTER);
                    // 设置单元格的四边边框
                    style.setBorderTop(BorderStyle.THIN);
                    style.setBorderBottom(BorderStyle.THIN);
                    style.setBorderLeft(BorderStyle.THIN);
                    style.setBorderRight(BorderStyle.THIN);

                    List<Integer> jshArr = new ArrayList<>();
                    List<Integer> jshSumArr = new ArrayList<>();

                    int num = 0;
                    // 插入填充数据
                    for (int i = 0; i < wgdList.size(); i++) {
                        num++;
                        Integer currentRow = 7+i;
                        // 填充数据
                        JSONObject jsonObject = wgdList.getJSONObject(i);
                        if (jsonObject.getStr("yqmc").contains("校准总费用")) {
                            String val = "SUM(G"+jshArr.get(0)+":G"+jshArr.get(jshArr.size()-1)+")";
                            Row newRow = sheetData.createRow(currentRow);  // 当前行
                            Cell zero = newRow.createCell(0);   // 序号
                            Cell one = newRow.createCell(1);    // 仪器名称
                            Cell six = newRow.createCell(6);   // 小计
                            // 设置值
//                            zero.setCellValue(jsonObject.getStr("bh"));
                            zero.setCellValue(num);
//                            one.setCellValue(jsonObject.getStr("yqmc"));
                            one.setCellValue("合计金额");
                            six.setCellFormula(val);
                            // 设置样式
                            zero.setCellStyle(style);
                            one.setCellStyle(style);
                            six.setCellStyle(style);
                            newRow.createCell(2).setCellStyle(style);
                            newRow.createCell(3).setCellStyle(style);
                            newRow.createCell(4).setCellStyle(style);
                            newRow.createCell(5).setCellStyle(style);
                            newRow.createCell(7).setCellStyle(style);
                            newRow.createCell(8).setCellStyle(style);
                            newRow.setHeightInPoints(25);
                            jshSumArr.add(setZfy(currentRow));
                            break;
                        }else {
                            // 当前是填充数据
                            Row newRow = sheetData.createRow(currentRow);  // 当前行
                            Cell zero = newRow.createCell(0);   // 序号
                            Cell one = newRow.createCell(1);    // 仪器名称
                            Cell two = newRow.createCell(2);    // 型号规格
                            Cell three = newRow.createCell(3);  // 编号
                            Cell four = newRow.createCell(4);   // 单价
                            Cell five = newRow.createCell(5);   // 数量
                            Cell six = newRow.createCell(6);   // 小计
                            Cell seven = newRow.createCell(7);   // 服务方式
                            Cell eight = newRow.createCell(8);   // 备注
                            // 每一行设置样式
//                            zero.setCellValue(jsonObject.getStr("bh"));
                            zero.setCellValue(num);
                            one.setCellValue(jsonObject.getStr("yqmc"));
                            two.setCellValue(jsonObject.getStr("xh"));
                            // 判断当前报价单的编号使用完工单的哪个编号
                            if (StringUtils.isNotEmpty(bo.getBjbh()) && "ccbh".equals(bo.getBjbh())) {
                                three.setCellValue(jsonObject.getStr("ccbh"));
                            }else if (StringUtils.isNotEmpty(bo.getBjbh()) && "glbh".equals(bo.getBjbh())) {
                                three.setCellValue(jsonObject.getStr("glbh"));
                            }
                            if(jsonObject.getStr("yqmc").contains("现场服务费")) {
                                // 设置仪器名称
                                one.setCellValue("下场费");
                                four.setCellValue("");
                                five.setCellValue("");
                                Double xj = jsonObject.getDouble("xj");
                                six.setCellValue(xj != null ? xj : 0);
                                jshSumArr.add(setZfy(currentRow));
                            }else {
                                Double dj = jsonObject.getDouble("dj");
                                four.setCellValue(dj != null ? dj : 0);
                                Double sl = jsonObject.getDouble("sl");
                                five.setCellValue(sl != null ? sl : 0);
                                String sjZm = new CellReference(currentRow, 4, false, false).formatAsString();
                                String djZm = new CellReference(currentRow, 5, false, false).formatAsString();
                                six.setCellFormula(sjZm + "*" + djZm);
                            }
                            seven.setCellValue(jsonObject.getStr("fwfs"));
                            eight.setCellValue(jsonObject.getStr("bz"));
                            // 先设置单元格演示
                            zero.setCellStyle(style);
                            one.setCellStyle(style);
                            two.setCellStyle(style);
                            three.setCellStyle(style);
                            four.setCellStyle(style);
                            five.setCellStyle(style);
                            six.setCellStyle(style);
                            seven.setCellStyle(style);
                            eight.setCellStyle(style);
                            // 设置计算列
                            jshArr.add(setZfy(currentRow));
                            newRow.setHeightInPoints(25);
                        }
                    }
                    // ==================== 生成图片  ================
                    // 获取行和列
                    int lastRowNum = sheetData.getLastRowNum();
                    int rowIndex = lastRowNum - 8; // 倒数第10行
                    int columnIndex = 6; // 第2列
                    for (int row = 0; row <= sheetData.getLastRowNum(); row++){
                        Row rowData = sheetData.getRow(row);
                        if (rowData != null) {
                            Cell cell = rowData.getCell(0);
                            if (cell != null && StringUtils.isNotEmpty(cell.toString()) && cell.toString().contains("（11）")) {
                                rowIndex = rowData.getRowNum();
                                break;
                            }
                        }
                    }
                    // 创建一个绘图对象
                    Drawing<?> drawing = sheetData.createDrawingPatriarch();
                    ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, columnIndex, rowIndex, columnIndex + 3, rowIndex + 13);
                    // 获取图片路径
                    String imgUrl = getTemplatePath("template/dafeng.png");
                    // 添加图片
                    byte[] imageBytes = getImageBytes(imgUrl);
                    int pictureIndex = fileWorkbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_PNG);
                    Picture picture = drawing.createPicture(anchor, pictureIndex);
                    picture.resize(0.6);
                    // ==================== 生成图片  ================

                    // 获取当前下载excel的路径
                    LocalDate currentDate = LocalDate.now();
                    // 分别获取年、月、日
                    int year = currentDate.getYear();
                    int monthValue = currentDate.getMonthValue();
                    int dayOfMonth = currentDate.getDayOfMonth();
                    // 生成Excel路径
                    String suffixPath = "/" + year + "/" + monthValue + "/" + dayOfMonth + "/" + "wgdToBjd/";
                    // 判断当前文件路径的文件夹是否存在，不存在则创建
                    if (!Files.exists(Paths.get(uploadPath + suffixPath))) {
                        // 路径不存在，尝试创建
                        try {
                            Files.createDirectories(Paths.get(uploadPath + suffixPath));
                        } catch (Exception e) {
                            System.err.println("创建目录时出错: " + e.getMessage());
                        }
                    }
                    // 报价单名称 日期+报价单+单位名称
                    String rq = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
                    String fileName = year + monthValue + dayOfMonth + "报价单" + bo.getKhname()+"_"+ rq + ".xlsx";
                    String path = uploadPath + suffixPath + fileName;
                    fileOutputStream = new FileOutputStream(path);
                    fileWorkbook.write(fileOutputStream);
                    fileWorkbook.close();
                    // 将当前的文件保存到附件表中
                    ZFujianBo fujian = new ZFujianBo();
                    fujian.setFjid(IdUtil.simpleUUID());  //设置附件id
                    fujian.setFjname(fileName);   //设置附件名称
                    fujian.setFjpath(suffixPath + fileName);  //设置附件路径
                    fujian.setFjtype(".xlsx");
                    fujianService.insertByBo(fujian);

                    // 查询当前的完工单数据
                    ZWgd zWgd = baseMapper.selectById(bo.getId());
                    String lsbjdid = zWgd.getLsbjdid();
                    if (StringUtils.isNotEmpty(lsbjdid)) {
                        lsbjdid += fujian.getFjid() + ",";
                    }else {
                        lsbjdid = fujian.getFjid() + ",";
                    }
                    // 修改当前的报价单历史报价单id
                    zWgd.setLsbjdid(lsbjdid);
                    baseMapper.updateById(zWgd);
                    return fujian;
                } catch (IOException e) {
                    e.printStackTrace();
                } catch (URISyntaxException e) {
                    throw new RuntimeException(e);
                } finally {
                    if (fis != null) {
                        try {
                            fis.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                    if (fos != null) {
                        try {
                            fos.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                    if (fileInputStream != null) {
                        try {
                            fileInputStream.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                    if (fileOutputStream != null) {
                        try {
                            fileOutputStream.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                }
            }
        }
        return null;
    }

    public char numberToLetter(int number) {
        char letter;
        if (number < 1 || number > 26) {
            throw new IllegalArgumentException("Number must be between 1 and 26.");
        }
        return (char) ('A' + number - 1);
    }

    // 获取excel的客户名称和日期，判断当前列表的表头在第一行，以及一个表头在第几列
    private JSONObject getDatas(Sheet sheet, int lastRowNum) {
        JSONObject jsonObject = new JSONObject();
        boolean xh = false; boolean yqmc = false; boolean xhgg = false; boolean zzc = false;
        boolean ccbh = false; boolean glbh = false; boolean sl = false; boolean dj = false; boolean xj = false;
        boolean fwfs = false; boolean bz = false;
        a:for (int i = 0; i < lastRowNum; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    if (cell != null) {
                        String cellValue = cell.toString().trim();
                        if ("序号".equals(cellValue)  && !xh) { // 判断当前的序号位置
                            jsonObject.put("xh_row", i);
                            jsonObject.put("xh_column",j);
                            xh = true;
                        }else if ("仪器名称".equals(cellValue)  && !yqmc) { // 判断当前的仪器名称位置
                            jsonObject.put("yqmc_row", i);
                            jsonObject.put("yqmc_column", j);
                            yqmc = true;
                        }else if ("型号/规格".equals(cellValue)  && !xhgg) { // 判断当前的型号/规格位置
                            jsonObject.put("xhgg_row", i);
                            jsonObject.put("xhgg_column", j);
                            xhgg = true;
                        }else if ("制造厂".equals(cellValue)  && !zzc) { // 判断当前的制造厂位置
                            jsonObject.put("zzc_row", i);
                            jsonObject.put("zzc_column", j);
                            zzc = true;
                        }else if ("出厂编号".equals(cellValue)  && !ccbh) { // 判断当前的出厂编号位置
                            jsonObject.put("ccbh_row", i);
                            jsonObject.put("ccbh_column", j);
                            ccbh = true;
                        }else if ("管理编号".equals(cellValue)  && !glbh) { // 判断当前的管理编号位置
                            jsonObject.put("glbh_row", i);
                            jsonObject.put("glbh_column", j);
                            glbh = true;
                        }else if ("数量".equals(cellValue)  && !sl) { // 判断当前的数量位置
                            jsonObject.put("sl_row", i);
                            jsonObject.put("sl_column", j);
                            sl = true;
                        }else if ("单价（元）".equals(cellValue)  && !dj) { // 判断当前的单价（元）位置
                            jsonObject.put("dj_row", i);
                            jsonObject.put("dj_column", j);
                            dj = true;
                        }else if ("小计（元)".equals(cellValue)  && !xj) { // 判断当前的小计（元)位置
                            jsonObject.put("xj_row", i);
                            jsonObject.put("xj_column", j);
                            xj = true;
                        }else if ("服务方式".equals(cellValue)  && !fwfs) { // 判断当前的服务方式位置
                            jsonObject.put("fwfs_row", i);
                            jsonObject.put("fwfs_column", j);
                            fwfs = true;
                        }else if ("备注".equals(cellValue)  && !bz) { // 判断当前的备注位置
                            jsonObject.put("bz_row", i);
                            jsonObject.put("bz_column", j);
                            bz = true;
                        }

                    }
                }
            }

        }
        return jsonObject;
    }

    // 设置当前的行
    private Integer setZfy(Integer currentRow) {
        Integer currentColumn = currentRow + 1;
        return currentColumn;
    }

    private String getTemplatePath(String path) throws URISyntaxException {
        // 使用当前类的 Class 对象来获取资源
        ClassLoader classLoader = ZWgdServiceImpl.class.getClassLoader();
        // 使用 getResource 方法获取 test.xlsx 文件的 URL
//        URL resourceUrl = classLoader.getResource("template/bjdmb1.xlsx");
        URL resourceUrl = classLoader.getResource(path);
        if (resourceUrl == null) {
            System.out.println("Resource not found.");
            return null;
        }
        // 将 URL 转换为文件路径
        return Paths.get(resourceUrl.toURI()).toString();
    }

    private static byte[] getImageBytes(String imagePath) throws IOException {
        try (FileInputStream fis = new FileInputStream(imagePath)) {
            byte[] bytes = new byte[fis.available()];
            fis.read(bytes);
            return bytes;
        }
    }

    /**
     * 生成报价单数据
     * @param bo
     * @return
     */
    @Override
    public byte[] genBjdData(ZFujianBo bo) {
        if (StringUtils.isNotEmpty(bo.getFjid())) {
            List<ZFujianVo> zFujianVos = fujianService.queryListByIdOrdery(bo.getFjid());
            for (ZFujianVo zFujianVo : zFujianVos) {
                String fjpath = uploadPath + zFujianVo.getFjpath();
                FileInputStream fis = null;
                FileOutputStream fos = null;
                try  {
                    fis = new FileInputStream(fjpath);
                    Workbook workbook = new XSSFWorkbook(fis);
                    Sheet sheet = workbook.getSheetAt(0); // 假设我们要操作第一个sheet
                    JSONArray array = new JSONArray();
                    for (int i = 7; i <= sheet.getLastRowNum(); i++) {
                        Row row = sheet.getRow(i);
                        if (row.getCell(0) !=null && StringUtils.isNotEmpty(row.getCell(0).toString())
                            && row.getCell(1) !=null && StringUtils.isNotEmpty(row.getCell(1).toString())
                            && row.getCell(2) !=null && StringUtils.isNotEmpty(row.getCell(2).toString())
                            && row.getCell(4) !=null && StringUtils.isNotEmpty(row.getCell(4).toString())
                            && row.getCell(5) !=null && StringUtils.isNotEmpty(row.getCell(5).toString())) {
                            JSONObject object = new JSONObject();
                            object.set("bh", row.getCell(0) !=null ? row.getCell(0).toString() : "");
                            object.set("yqmc", row.getCell(1) !=null ? row.getCell(1).toString() : "");
                            object.set("ggxh", row.getCell(2) !=null ? row.getCell(2).toString() : "");
                            object.set("glbh", row.getCell(3) !=null ? row.getCell(3).toString() : "");
                            object.set("dj", row.getCell(4) !=null ? Double.parseDouble(row.getCell(4).toString()) : "");
                            object.set("sl", row.getCell(5) !=null ? Double.parseDouble(row.getCell(5).toString()) : "");
                            object.set("xj", row.getCell(6) !=null ? Double.parseDouble(row.getCell(6).toString()) : "");
                            object.set("fwfs", row.getCell(7) !=null ? row.getCell(7).toString() : "");
                            object.set("bz", row.getCell(8) !=null ? row.getCell(8).toString() : "");
                            array.add(object);
                        }else if (row.getCell(1) != null
                                && StringUtils.isNotEmpty(row.getCell(1).toString())
                                && (row.getCell(1).toString().contains("校准总费用")
                                || row.getCell(1).toString().contains("现场服务费")
                                || row.getCell(1).toString().contains("含6%专用增值税发票"))
                        ) {
                            JSONObject object = new JSONObject();
                            object.set("bh", row.getCell(0) !=null ? row.getCell(0).toString() : "");
                            object.set("yqmc", row.getCell(1) !=null ? row.getCell(1).toString() : "");
                            object.set("ggxh", row.getCell(2) !=null ? row.getCell(2).toString() : "");
                            object.set("glbh", row.getCell(3) !=null ? row.getCell(3).toString() : "");
                            Cell four = row.getCell(4);
                            if (four != null && StringUtils.isNotEmpty(four.toString())) {
                                object.set("dj", Double.parseDouble(four.toString()));
                            }else {
                                object.set("dj", "");
                            }
                            Cell five = row.getCell(5);
                            if (five != null && StringUtils.isNotEmpty(five.toString())) {
                                object.set("sl", Double.parseDouble(five.toString()));
                            }else {
                                object.set("sl", "");
                            }
                            object.set("xj", row.getCell(6) !=null ? row.getCell(6).toString() : "");
                            object.set("fwfs", row.getCell(7) !=null ? row.getCell(7).toString() : "");
                            object.set("bz", row.getCell(8) !=null ? row.getCell(8).toString() : "");
                            array.add(object);
                        }
                    }
                    // 设置样式
                    // 获取当前报价单路径
                    String path = getBjdPath();
                    String fileName = "报价单" +
                        new SimpleDateFormat("yyyyMMdd").format(new Date()) +
                        "_"+ IdUtil.simpleUUID() + ".xlsx";
                    fos = new FileOutputStream(path + fileName);
                    // 创建工作簿
                    Workbook workData = new XSSFWorkbook();
                    Font font = workData.createFont();
                    font.setFontName("SimSun");  // 设置字体为宋体
                    // 创建单元格样式
                    CellStyle style = workData.createCellStyle();
                    // 将字体应用到单元格样式
                    style.setFont(font);
                    // 设置单元格文字居中
                    style.setAlignment(HorizontalAlignment.CENTER);
                    style.setVerticalAlignment(VerticalAlignment.CENTER);
                    // 设置单元格的四边边框
                    style.setBorderTop(BorderStyle.THIN);
                    style.setBorderBottom(BorderStyle.THIN);
                    style.setBorderLeft(BorderStyle.THIN);
                    style.setBorderRight(BorderStyle.THIN);
                    // 创建工作表
                    Sheet sheetData = workData.createSheet();
                    Row row = sheetData.createRow(0);
                    // 设置第一行数据
                    row.createCell(0).setCellValue("序号");  row.getCell(0).setCellStyle(style);
                    row.createCell(1).setCellValue("仪器名称"); row.getCell(1).setCellStyle(style);
                    row.createCell(2).setCellValue("型号规格"); row.getCell(2).setCellStyle(style);
                    row.createCell(3).setCellValue("管理编号"); row.getCell(3).setCellStyle(style);
                    row.createCell(4).setCellValue("单价（元）"); row.getCell(4).setCellStyle(style);
                    row.createCell(5).setCellValue("数量");      row.getCell(5).setCellStyle(style);
                    row.createCell(6).setCellValue("小计（元）"); row.getCell(6).setCellStyle(style);
                    row.createCell(7).setCellValue("服务方式"); row.getCell(7).setCellStyle(style);
                    row.createCell(8).setCellValue("备注");     row.getCell(8).setCellStyle(style);
                    sheetData.setColumnWidth(0, 20 * 256 + 400);
                    // 最后行
                    List<Integer> xjArr = new ArrayList<>();
                    List<Integer> sumArr = new ArrayList<>();
                    // 将内容写到Excel中
                    a:for (int i = 0; i < array.size(); i++) {
                        JSONObject jsonObject = array.getJSONObject(i);
                        Row rowData = sheetData.createRow(i + 1);
                        sheetData.setColumnWidth(i + 1, 20 * 256 + 400);
                        rowData.createCell(0).setCellStyle(style);
                        rowData.createCell(1).setCellStyle(style);
                        rowData.createCell(2).setCellStyle(style);
                        rowData.createCell(3).setCellStyle(style);
                        rowData.createCell(4).setCellStyle(style);
                        rowData.createCell(5).setCellStyle(style);
                        rowData.createCell(6).setCellStyle(style);
                        rowData.createCell(7).setCellStyle(style);
                        rowData.createCell(8).setCellStyle(style);
                        rowData.setHeightInPoints(25);
                        String yqmc = jsonObject.getStr("yqmc");
                        if (StringUtils.isNotEmpty(yqmc)
                            && (yqmc.contains("校准总费用"))) {
                            String val = "SUM(G"+xjArr.get(0)+":G"+xjArr.get(xjArr.size()-1)+")";
                            rowData.getCell(0).setCellValue(jsonObject.getStr("bh"));
                            rowData.getCell(1).setCellValue("校准总费用：");  // 设置仪器名称
                            rowData.getCell(6).setCellFormula(val);
                            sumArr.add(setZfy(i + 1));
                        }else if (StringUtils.isNotEmpty(yqmc)
                            && (yqmc.contains("含6%专用增值税发票"))) {
                            String val = "SUM(G"+sumArr.get(sumArr.size()-2)+":G"+sumArr.get(sumArr.size()-1)+")";
                            rowData.getCell(0).setCellValue(jsonObject.getStr("bh"));
                            rowData.getCell(1).setCellValue("含6%专用增值税发票，合计总费用：");  // 设置仪器名称
                            rowData.getCell(6).setCellFormula(val);
                            break a;
                        }else if (StringUtils.isNotEmpty(yqmc)
                            && (yqmc.contains("现场服务费"))) {
                            rowData.getCell(0).setCellValue(jsonObject.getStr("bh"));
                            rowData.getCell(1).setCellValue(jsonObject.getStr("yqmc"));
                            rowData.getCell(2).setCellValue(jsonObject.getStr("ggxh"));
                            rowData.getCell(3).setCellValue(jsonObject.getStr("glbh"));
                            if (jsonObject.getDouble("dj") != null) {
                                rowData.getCell(4).setCellValue(jsonObject.getDouble("dj"));
                            }else {
                                rowData.getCell(4).setCellValue("");
                            }
                            if (jsonObject.getDouble("sl") != null) {
                                rowData.getCell(4).setCellValue(jsonObject.getDouble("sl"));
                            }else {
                                rowData.getCell(4).setCellValue("");
                            }
                            rowData.getCell(6).setCellValue(jsonObject.getDouble("xj") != null ? jsonObject.getDouble("xj") : 0);
                            rowData.getCell(7).setCellValue(jsonObject.getStr("fwfs"));
                            rowData.getCell(8).setCellValue(jsonObject.getStr("bz"));
                            sumArr.add(setZfy(i + 1));
                        }else {
                            rowData.getCell(0).setCellValue(jsonObject.getStr("bh"));
                            rowData.getCell(1).setCellValue(jsonObject.getStr("yqmc"));
                            rowData.getCell(2).setCellValue(jsonObject.getStr("ggxh"));
                            rowData.getCell(3).setCellValue(jsonObject.getStr("glbh"));
                            rowData.getCell(4).setCellValue(jsonObject.getDouble("dj") != null ? jsonObject.getDouble("dj") : 0);
                            rowData.getCell(5).setCellValue(jsonObject.getDouble("sl") != null ? jsonObject.getDouble("sl") : 0);
                            rowData.getCell(6).setCellValue(jsonObject.getDouble("xj") != null ? jsonObject.getDouble("xj") : 0);
                            rowData.getCell(7).setCellValue(jsonObject.getStr("fwfs"));
                            rowData.getCell(8).setCellValue(jsonObject.getStr("bz"));
                            xjArr.add(setZfy(i + 1));
                            sumArr.add(setZfy(i + 1));
                        }
                    }
                    // 写入文件
                    workData.write(fos);
                    // 关闭资源
                    fis.close();
                    workData.close();
                    // 获取当前的文件流
                    FileInputStream inputStream = new FileInputStream(path + fileName);
                    byte[] bytes = IOUtils.toByteArray(inputStream);
                    inputStream.close();
                    return bytes;
                }catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    if (fis != null) {
                        try {
                            fis.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                    if (fos != null) {
                        try {
                            fos.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                }
            }
        }
        return null;
    }

    @Override
    public List<ZWgdVo> selectByZbbhId(String zbbh) {
        List<ZWgdVo> zWgdVos = baseMapper.selectByZbbhId(zbbh);
        return zWgdVos;
    }

    public String getBjdPath() {
        // 获取当前下载excel的路径
        LocalDate currentDate = LocalDate.now();
        // 分别获取年、月、日
        int year = currentDate.getYear();
        int monthValue = currentDate.getMonthValue();
        int dayOfMonth = currentDate.getDayOfMonth();
        String suffixPath = "/" + year + "/" + monthValue + "/" + dayOfMonth + "/" + "bjdData/";
        String path = uploadPath + suffixPath;
        // 判断当前文件路径的文件夹是否存在，不存在则创建
        if (!Files.exists(Paths.get(uploadPath + suffixPath))) {
            // 路径不存在，尝试创建
            try {
                Files.createDirectories(Paths.get(uploadPath + suffixPath));
            } catch (Exception e) {
                System.err.println("创建目录时出错: " + e.getMessage());
                throw new RuntimeException("创建目录时出错");
            }
        }
        return path;
    }

}
